﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using S4Store.Domain.ExhibitionKpi;
using S4Store.Domain.NetMarketingKpi;
using S4Store.Domain.NetMarketingKpiReport;
using S4Store.Models.Exports;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.XSSF.UserModel;
using S4Store.Service;
using NPOI.SS.Util;
using S4Store.Domain;
using LX.Web.Excel;

namespace S4Store.Controllers
{
    public class ExportController : ReportBaseController
    {
        public FinancialReportService FinancialReportService { get; set; }
        public FinancialKpiService FinancialKpiService { get; set; }

        public TelSaleReportService TelSaleReportService { get; set; }
        public TelSaleKpiService TelSaleKpiService { get; set; }

        public NetMarketingKpiService NetMarketingKpiService { get; set; }
        public NetMarketingKpiReportService NetMarketingKpiReportService { get; set; }

        public ExhibitionReportService ExhibitionReportService { get; set; }

        public ReportService ReportService { get; set; }
        public SellerKpiService SellerKpiService { get; set; }
        public CarService CarService { get; set; }
        public CarKpiService CarKpiService { get; set; }
        public AfterSaleCarKPIService AfterSaleCarKPIService { get; set; }
        public EmployeeService EmployeeService { get; set; }
        public SparePartService SparePartService { get; set; }
        //
        // GET: /Import/
        
        public ActionResult Index()
        {
            IndexModel model = new IndexModel();
            this.InjectReportModel(model);
            model.BarModel.AddView(Models.ReportBarModel.ViewItem.Month);
            model.BarModel.RemoveView(Models.ReportBarModel.ViewItem.SubmitButton);
            model.BarModel.Action = "Index";
            model.BarModel.Controller = "Export";
            //model.BarModel.
            return View(model);
        }

        #region 销售
        public ActionResult CarKpi()
        {
            using (var t = new Transtion())
            {
                t.Inject(ReportService);
                t.Inject(CarKpiService);

                var carmodels = CarKpiService.GetCarmodelsForStoreMonth(Month.Year, Month.Month, Store.Id).ToList();
                var carbrands = from m in carmodels
                                group m by m.Brand into g
                                select g.Key;
                ExcelDocument exceldoc = new ExcelDocument(Server.MapPath("/Templates/Export/车型KPI.xlsx"));
                ExcelSheet sheet = exceldoc.GetSheet(0);
                ExcelSheet sheet1 = exceldoc.GetSheet(1);
                sheet.Columns = new List<string>
                {
                    "车型|合计标题","车款","上月库存","上月交车","上月库存系数",
                    "二网已订","本店已订","累计订车","订单目标","订单进度","订单进度差距",
                    "A类库存","B类库存","C类库存","C类库龄","库存合计",
                    "二网交车","本店交车","交车合计",
                    "销售目标","销售进度","销售进度差距","库存系数",
                    "H级有望客户","A级有望客户","B级有望客户","有望客户合计",
                    "H级登记客户","A级登记客户","B级登记客户","登记客户合计",
                    "累计投保","投保比率","投保比率差距",
                    "累计置换","置换比率","置换比率差距",
                    "累计按揭","按揭比率","按揭比率差距",
                    "累计延保","延保比率","延保比率差距"
                };
                sheet1.Columns = new List<string>
                {
                    "车型|合计标题","车款","本店交车","标配车型计数","套餐车更换率",
                    "是否上旬套餐版本","上旬版本计数","上旬套餐版本交车量",
                    "是否中旬套餐版本","中旬版本计数","中旬套餐版本交车量",
                    "是否下旬套餐版本","下旬版本计数","下旬套餐版本交车量",
                    "累计版本计数","装潢总额","装潢车交车量",
                    "套餐车销售量","套餐车销售率","套餐车销售率差","装潢车销售率","单车装潢总额"
                };
                sheet.AutoSizeColumns = new List<string> { 
                   "车型|合计标题", "车款","订单进度差距"
                };
                sheet1.AutoSizeColumns = new List<string> { 
                   "车型|合计标题", "车款"
                };
                foreach (var carbrand in carbrands)
                {
                    foreach (var carmodel in carmodels.Where(x => x.BrandId == carbrand.Id))
                    {
                        var modelreport = ReportService.GetCarModelSaleReport(Month.Year, Month.Month, Store.Id, carmodel.Id);
                        sheet.AddRow(modelreport);
                        sheet1.AddRow(modelreport);
                    }
                    if (carmodels.Count(x => x.BrandId == carbrand.Id) > 0)
                    {
                        var brandreport = ReportService.GetCarBrandSaleReport(Month.Year, Month.Month, Store.Id, carbrand.Id);
                        sheet.AddRow(brandreport, ExcelCellBg.Group);
                        sheet1.AddRow(brandreport, ExcelCellBg.Group);
                    }
                }
                if (carbrands.Count() > 0)
                {
                    var allreport = ReportService.GetSaleReport(Month.Year, Month.Month, Store.Id);
                    sheet.AddRow(allreport, ExcelCellBg.Sum);
                    sheet1.AddRow(allreport, ExcelCellBg.Sum);
                }
                sheet.Fill();
                sheet1.Fill();
                return File(exceldoc.GetBinaryData(), "application/vnd ms-excel", "车型KPI(" + Month.Year + "-" + Month.Month + ").xlsx");

            }
        }


        public ActionResult SellerKpi()
        {
            using (var t = new Transtion())
            {
                t.Inject(ReportService);
                t.Inject(EmployeeService);
                t.Inject(CarKpiService);
                var sellers = EmployeeService.GetEnableEmployee(Store.Id, EmployeeType.FieldSaler).ToList();
                var brands = CarKpiService.GetCarbrandsForStoreMonth(Month.Year, Month.Month, Store.Id).ToList();

                ExcelDocument exceldoc = new ExcelDocument(Server.MapPath("/Templates/Export/展厅销售Kpi.xlsx"));
                ExcelSheet sheet = exceldoc.GetSheet(0);
                sheet.Columns = new List<string>
                {
                    "销售顾问名称|合计标题","车型","首次来电接待目标","月初尚存意向客户数","首次来店接待批次",
                    "首次来店登记数目标","首次来店客户登记数","有效A卡建卡数","试驾邀请书填写数","试驾客户未成交数","商谈明细表填写数",
                    "超限价格成交数","二手车评估数","置换成交数","按揭成交数","上月遗留客户新增订单","当月客户新增订单数","交车数",
                    "新车保险投保数","保险销售额","套餐车销售数量","有效装潢车销售数量","装潢销售额","新车延保数","二次成功邀约客户数",
                    "老客户推荐成交数","销售目标","销量进度","销量进度差","来店批次完成进度","来店批次进度差","当月登记客户完成进度",
                    "登记客户进度率差","首次来店登记率","A卡需求完整分析率","试乘试驾率","试驾未成交率","商谈明细表填写率",
                    "上月客户转化率","当月登记客户成交率","订单交车转化率","超限价格成交率","二手车评估率","当月置换率","当月保险率",
                    "当月按揭成交率","平均单车保险额","套餐车销售率","有效装潢车销售率","平均单车装潢额","新车延保实现率","二次邀约成功率",
                    "老客户推荐成功率"
                };
                sheet.AutoSizeColumns = new List<string> { 
                   "销售顾问名称|合计标题", "车型"
                };
                foreach (var seller in sellers)
                {
                    foreach (var brand in brands)
                    {
                        var brandreport = ReportService.GetSellerPersonBrandReport(Month.Year, Month.Month, Store.Id,seller.Id, brand.Id);
                        sheet.AddRow(brandreport);
                    }
                    if (brands.Count > 0)
                    {
                        var sellerreport = ReportService.GetSellerPersonReport(Month.Year, Month.Month, Store.Id, seller.Id);
                        sheet.AddRow(sellerreport, ExcelCellBg.Group);
                    }
                }
                if (sellers.Count() > 0)
                {
                    var allreport = ReportService.GetSellerReport(Month.Year, Month.Month, Store.Id);
                    sheet.AddRow(allreport, ExcelCellBg.Sum);
                }
                sheet.Fill();
                t.Commit();
                return File(exceldoc.GetBinaryData(), "application/vnd ms-excel", "展厅销售Kpi(" + Month.Year + "-" + Month.Month + ").xlsx");

            }
        }
        public ActionResult TelSellerKpi()
        {
            using (var t = new Transtion())
            {
                t.Inject(ReportService);
                t.Inject(EmployeeService);
                t.Inject(CarKpiService);
                var sellers = EmployeeService.GetEnableEmployee(Store.Id, EmployeeType.TelSaler).ToList();
                var brands = CarKpiService.GetCarbrandsForStoreMonth(Month.Year, Month.Month, Store.Id).ToList();

                ExcelDocument exceldoc = new ExcelDocument(Server.MapPath("/Templates/Export/电话销售Kpi.xlsx"));
                ExcelSheet sheet = exceldoc.GetSheet(0);
                sheet.Columns = new List<string>
                {
                    "销售顾问名称|合计标题","车型","首次来电接待目标","月初尚存意向客户数","首次来店接待批次",
                    "首次来店登记数目标","首次来店客户登记数","有效A卡建卡数","试驾邀请书填写数","试驾客户未成交数","商谈明细表填写数",
                    "超限价格成交数","二手车评估数","置换成交数","按揭成交数","上月遗留客户新增订单","当月客户新增订单数","交车数",
                    "新车保险投保数","保险销售额","套餐车销售数量","有效装潢车销售数量","装潢销售额","新车延保数","二次成功邀约客户数",
                    "老客户推荐成交数","销售目标","销量进度","销量进度差","来店批次完成进度","来店批次进度差","当月登记客户完成进度",
                    "登记客户进度率差","首次来店登记率","A卡需求完整分析率","试乘试驾率","试驾未成交率","商谈明细表填写率",
                    "上月客户转化率","当月登记客户成交率","订单交车转化率","超限价格成交率","二手车评估率","当月置换率","当月保险率",
                    "当月按揭成交率","平均单车保险额","套餐车销售率","有效装潢车销售率","平均单车装潢额","新车延保实现率","二次邀约成功率",
                    "老客户推荐成功率"
                };
                sheet.AutoSizeColumns = new List<string> { 
                   "销售顾问名称|合计标题", "车型"
                };
                foreach (var seller in sellers)
                {
                    foreach (var brand in brands)
                    {
                        var brandreport = ReportService.GetSellerPersonBrandReport(Month.Year, Month.Month, Store.Id, seller.Id, brand.Id);
                        sheet.AddRow(brandreport);
                    }
                    if (brands.Count > 0)
                    {
                        var sellerreport = ReportService.GetSellerPersonReport(Month.Year, Month.Month, Store.Id, seller.Id);
                        sheet.AddRow(sellerreport, ExcelCellBg.Group);
                    }
                }
                if (sellers.Count() > 0)
                {
                    var allreport = ReportService.GetSellerReport(Month.Year, Month.Month, Store.Id, EmployeeType.TelSaler);
                    sheet.AddRow(allreport, ExcelCellBg.Sum);
                }
                sheet.Fill();
                t.Commit();
                return File(exceldoc.GetBinaryData(), "application/vnd ms-excel", "电话销售Kpi(" + Month.Year + "-" + Month.Month + ").xlsx");

            }
        }
        #endregion

        #region 财务

        public ActionResult FinancialKpi()
        {
            using (var t = new Transtion())
            {
                t.Inject(FinancialReportService);
                t.Inject(FinancialKpiService);

                var carbrands = FinancialKpiService.GetCarbrandsForStoreMonth(Month.Year, Month.Month, Store.Id).ToList();

                ExcelDocument exceldoc = new ExcelDocument(Server.MapPath("/Templates/Export/财务数据报表.xlsx"));
                ExcelSheet sheet0 = exceldoc.GetSheet(0);
                ExcelSheet sheet1 = exceldoc.GetSheet(1);
                ExcelSheet sheet2 = exceldoc.GetSheet(2);
                sheet0.Columns = new List<string>
                {
                    "车型|合计标题","月初库存成本","月末库存成本","月库存平均成本","本月车辆销售成本",
                    "库存资金周转天","目标周转天数|目标周转天数1","库存资金周转天差异","本月售后总产值","本月4S店运营成本|本月4S店运营成本1","本月服务吸收率",
                    "服务吸收率贡献度","服务吸收率目标","服务吸收率差异"
                };
                sheet1.Columns = new List<string>
                {
                    "车型|合计标题","销售额","商品车销售额","装潢营业额","保险销售额",
                    "贷款销售额","二手车销售额","二手车毛利","延保销售额","商品车营业额占比","装潢营业额占比",
                    "保险营业额占比","贷款服务费占比","二手车毛利占比","延保营业额占比"
                };
                sheet2.Columns = new List<string>
                {
                    "车型|合计标题","本月售后总产值","事故车总产值","保养产值","维修产值",
                    "备件产值","装潢产值","养护产品产值","事故车产值占比","保养产值占比",
                    "维修产值占比","备件销售产值占比","装潢销售产值占比","养护产品产值占比"
                };
                sheet0.AutoSizeColumns = new List<string> { 
                   "车型|合计标题"
                };
                sheet1.AutoSizeColumns = new List<string> { 
                   "车型|合计标题"
                };
                sheet2.AutoSizeColumns = new List<string> { 
                   "车型|合计标题"
                };
                var report = FinancialReportService.GetStoreReport(Month.Year, Month.Month, Store.Id);
                foreach (var carbrand in carbrands)
                {
                    var brandreport = FinancialReportService.GetCarBrandReport(Month.Year, Month.Month, Store.Id, carbrand.Id);
                    brandreport.StoreReport = report;
                    sheet0.AddRow(brandreport);
                    sheet1.AddRow(brandreport);
                    sheet2.AddRow(brandreport);
                }
                if (carbrands.Count > 0)
                {
                    sheet0.AddRow(report, ExcelCellBg.Sum);
                    sheet1.AddRow(report, ExcelCellBg.Sum);
                    sheet2.AddRow(report, ExcelCellBg.Sum);
                }
                sheet0.Fill();
                sheet1.Fill();
                sheet2.Fill();


                return File(exceldoc.GetBinaryData(), "application/vnd ms-excel", "财务数据报表(" + Month.Year + "-" + Month.Month + ").xlsx");

            }
        }


        #endregion

        #region 营销

        public ActionResult TelSaleKpi()
        {
            using (var t = new Transtion())
            {
                t.Inject(TelSaleReportService);
                t.Inject(TelSaleKpiService);

                var carbrands = TelSaleKpiService.GetCarbrandsForStoreMonth(Month.Year, Month.Month, Store.Id).ToList();
                
                ExcelDocument exceldoc = new ExcelDocument(Server.MapPath("/Templates/Export/电话销售车型管理数据.xlsx"));
                ExcelSheet sheet = exceldoc.GetSheet(0);
                sheet.Columns = new List<string>
                {
                    "车型|合计标题","厂方广告获取信息数","自店广告获取信息数","电话登记表登记客户数","网站获取信息数",
                    "12小时电话回复数","线上转来店客户数","来店客户信息录入数","交车数","电话销售目标","获取信息总数",
                    "电话客户登记率","12小时电话回复率","线上客户转换率","来店客户信息录入率","电话销售进度",
                    "销售时间进度差","应时销量"
                };
                sheet.AutoSizeColumns = new List<string> { 
                   "车型|合计标题"
                };
                foreach (var carbrand in carbrands)
                {
                        var modelreport = TelSaleReportService.GetCarBrandReport(Month.Year, Month.Month, Store.Id, carbrand.Id);
                        sheet.AddRow(modelreport);
                    
                    
                }
                if (carbrands.Count > 0)
                {
                    var storereport = TelSaleReportService.GetStoreReport(Month.Year, Month.Month, Store.Id);
                    sheet.AddRow(storereport, ExcelCellBg.Group);
                }
                sheet.Fill();
                return File(exceldoc.GetBinaryData(), "application/vnd ms-excel", "电话销售车型管理数据(" + Month.Year + "-" + Month.Month + ").xlsx");

            }
        }

        public ActionResult NetMarketingKpi()
        {
            using (var t = new Transtion())
            {
                t.Inject(NetMarketingKpiReportService);
                t.Inject(NetMarketingKpiService);

                //var carbrands = TelSaleKpiService.GetCarbrandsForStoreMonth(Month.Year, Month.Month, Store.Id).ToList();

                ExcelDocument exceldoc = new ExcelDocument(Server.MapPath("/Templates/Export/网电营销Kpi.xlsx"));
                ExcelSheet sheet = exceldoc.GetSheet(0);

                sheet.Columns = new List<string>
                {
                    "媒体名称|合计标题","获取信息目标","月度费用",
                    "计划发帖数","实际发帖数","点击数","单击退出数","获取信息数","主动来电数",
                    "邀约来店数","资金投入比","发帖执行率","单击退出率",
                    "获取资源总数","集客进度","集客进度差距","获取信息比","获得电话比","关注度变化率","下线转化率"
                    ,"单组资源成本"
                };
                sheet.AutoSizeColumns = new List<string> { 
                   
                };
                var report = NetMarketingKpiReportService.GetStoreReport(Month.Year, Month.Month, Store.Id);

                sheet.SetHolderValue("来店量目标", report.NetMarketingKpiMonthConfig.LDLMB.ToString());
                sheet.SetHolderValue("下线转化率目标", report.NetMarketingKpiMonthConfig.XXZHLVMB.ToString() + "%");
                sheet.SetHolderValue("信息获取总目标", report.NetMarketingKpiMonthConfig.XXHQMB.ToString());
                sheet.SetHolderValue("月度预算", report.NetMarketingKpiMonthConfig.YDYS.ToString());
                sheet.SetHolderValue("网电营销实际投入", report.YDFY.ToString());
                sheet.SetHolderValue("营销资金执行率", report.YXZJZXLV.toInt(2)+"%");
                var mediawebs = NetMarketingKpiService.GetMedias(Store.Id).ToList().Where(x=>x.Type== NetMarketingMedia.MediaType.WebSite);
                if (mediawebs.Any())
                {
                    foreach (var media in mediawebs)
                    {
                        var mreport = NetMarketingKpiReportService.GetMediaReport(Month.Year, Month.Month, Store.Id,
                            media.Id);
                        sheet.AddRow(mreport);
                    }
                    var webreport = new NetMarketingMediaGroupReport(report, NetMarketingMedia.MediaType.WebSite);
                    sheet.AddRow(webreport, ExcelCellBg.Group);
                }
                var mediamobiles = NetMarketingKpiService.GetMedias(Store.Id).ToList().Where(x => x.Type == NetMarketingMedia.MediaType.Mobile);

                if (mediamobiles.Any())
                {
                    foreach (var media in mediamobiles)
                    {
                        var mreport = NetMarketingKpiReportService.GetMediaReport(Month.Year, Month.Month, Store.Id,
                            media.Id);
                        sheet.AddRow(mreport);
                    }
                    var mobilereport = new NetMarketingMediaGroupReport(report, NetMarketingMedia.MediaType.Mobile);
                    sheet.AddRow(mobilereport, ExcelCellBg.Group);
                }
                //if (mediawebs.Any() && mediamobiles.Any())
                //{
                //    sheet.AddRow(report, ExcelCellBg.Sum);
                //}

                sheet.Fill();
                return File(exceldoc.GetBinaryData(), "application/vnd ms-excel", "网电营销Kpi(" + Month.Year + "-" + Month.Month + ").xlsx");

            }
        }


        public ActionResult Exhibition()
        {
            using (var t = new Transtion())
            {
                t.Inject(ExhibitionReportService);

                //var carbrands = TelSaleKpiService.GetCarbrandsForStoreMonth(Month.Year, Month.Month, Store.Id).ToList();

                ExcelDocument exceldoc = new ExcelDocument(Server.MapPath("/Templates/Export/市场部展厅活动KPI.xlsx"));
                ExcelSheet sheet = exceldoc.GetSheet(0);

                sheet.Columns = new List<string>
                {
                    "执行月度|合计标题","执行周",
                    "活动主题","广宣预算月度分解数","广宣资金投入额","广宣计划投放数","广宣实际投放数","传统广告资金投放额","传统广告计划投放数",
                    "传统广告实际投放数","广告招徕电话批次","计划市场情报搜集次数","市场情报实际搜集报告数","交车数","交车客户面访调研数",
                    "主题活动主题","计划主题活动数","主题活动时间","主题活动实际执行数","主题活动投入资金","主题活动策划案成稿数","主题活动新增集客数"
                    ,"主题活动效果评估报告数",
                    "店头活动主题","计划店头活动数","店头活动执行时间","店头活动实际执行数","店头活动投入资金","店头活动策划案成稿数","店头活动目标邀约数"
                    ,"店头活动成功邀约客户数","店头活动战败客二次消化数","店头活动效果评估报告数",
                    "广宣预算资金到位率","广宣计划实施率","传统广告资金占用率","传统广告计划执行率","单次客户集客成本","传统广告集客贡献度",
                    "主题活动计划执行率","主题活动策划案成稿率","主题活动单次客户集客成本","主题活动集客贡献度","主题活动效果评估率","店头活动计划执行率",
                    "店头活动策划案成稿率","店头活动单次客户集客成本","店头活动邀约成功率","店头活动效果评估率","店头活动战败客二次消化率","当月情报收集执行率",
                    "客户调研分析率"

                };
                sheet.AutoSizeColumns = new List<string>
                {
                    "活动主题"
                };
                var report = ExhibitionReportService.GetExhibitionReport(Month.Year, Month.Month, Store.Id);

                sheet.SetHolderValue("销售目标", report.ExhibitionMonthConfig.XSMB.ToString());
                sheet.SetHolderValue("单台广告投放金额", report.ExhibitionMonthConfig.DTGGTFJE.ToString() );
                sheet.SetHolderValue("目标首次来店接待数", report.ExhibitionMonthConfig.NBSCLDJDS.ToString());
                sheet.SetHolderValue("上月邀约总数", report.ExhibitionMonthConfig.SYYYZS.ToString());
                for (int i = 1; i <= 8; i++)
                {
                    var weekreport = new ExhibitionWeekReport(report, i);
                    sheet.AddRow(weekreport);
                }
                sheet.AddRow(report, ExcelCellBg.Sum);
                sheet.Fill();
                return File(exceldoc.GetBinaryData(), "application/vnd ms-excel", "市场部展厅活动KPI(" + Month.Year + "-" + Month.Month + ").xlsx");

            }
        }

        #endregion

        #region 售后
        public ActionResult AfterSaleCarKpi()
        {
            using (var t = new Transtion())
            {
                t.Inject(ReportService);
                t.Inject(AfterSaleCarKPIService);

                var query = AfterSaleCarKPIService.GetAfterSaleCarTypeMonthConfigs(Month.Year, Month.Month, Store.Id).ToList();
                var carbrands = from m in query
                                group m by m.CarBrand into g
                                select g.Key;
                ExcelDocument exceldoc = new ExcelDocument(Server.MapPath("/Templates/Export/售后业绩车型KPI.xlsx"));
                ExcelSheet sheet = exceldoc.GetSheet(0);
                sheet.Columns = new List<string>
                {
                    "车型","本月应实施5K保养数","本月应实施10K保养数","本月事故车入厂台次目标","本月维修车入厂台次目标","目标入厂台次",
                    "5k保养目标产值","10k保养目标产值","本月事故车维修产值目标","本月维修车产值目标","目标售后产值",
                    "首次续保台次目标","二次续保台次目标","目标续保台次总数","目标单车产值","累计完成事故入厂台次","累计完成维修入厂台次",
                    "本月实际实施5K保养数","本月实际实施10K保养数","累计完成入厂台次","5K保养实际产值","10K保养实际产值","其他维修产值",
                    "累计完成事故车产值","累计常规维修产值","累计完成售后总产值","累计精品销售额","累计养护产品销售额","首次续保实际完成台次",
                    "二次续保实际完成台次","续保台次完成总数","本月5K实施完成率","本月5K实施完成进度差","本月10K实施完成率","本月10K实施完成进度差",
                    "事故车入厂台次完成率","事故车入厂台次完成进度差","维修车入厂台次完成率","维修车入厂台次完成进度差","总目标入厂台次完成率",
                    "总目标入厂台次完成进度差","事故车产值完成率","事故车产值完成进度差","维修车产值完成率","维修车产值完成进度差","目标产值完成率",
                    "目标产值完成进度差","实际平均单车产值","单车产值完成度","首次续保完成率","首次续保完成进度差","二次续保完成率",
                    "二次续保完成进度差","续保目标完成率","续保目标完成进度差"
                };
                sheet.AutoSizeColumns = new List<string> { 
                    
                };
                foreach (var carbrand in carbrands)
                {
                    var brandreport = AfterSaleCarKPIService.GetCarTypeReport(Month.Year, Month.Month, Store.Id, carbrand.Id);
                    brandreport.CarBrand = carbrand.Name;
                    sheet.AddRow(brandreport);
                }
                var brandreportTotal = AfterSaleCarKPIService.GetCarTypeReport(Month.Year, Month.Month, Store.Id);
                brandreportTotal.CarBrand = "合计";
                sheet.AddRow(brandreportTotal);

                sheet.Fill();
                return File(exceldoc.GetBinaryData(), "application/vnd ms-excel", "售后业绩车型KPI(" + Month.Year + "-" + Month.Month + ").xlsx");

            }
        }

        public ActionResult DecoCarKpi()
        {
            using (var t = new Transtion())
            {
                t.Inject(ReportService);
                t.Inject(AfterSaleCarKPIService);

                var query = AfterSaleCarKPIService.GetAfterSaleCarDecoratingDayInputs(Month.Year, Month.Month, Store.Id).ToList();
                var carbrands = from m in query
                                group m by m.CarBrand into g
                                select g.Key;
                ExcelDocument exceldoc = new ExcelDocument(Server.MapPath("/Templates/Export/车型装潢.xlsx"));
                ExcelSheet sheet = exceldoc.GetSheet(0);
                sheet.Columns = new List<string>
                {
                    "车型","装潢率目标","装潢额目标","目标单车装潢销售额","本月交车累计","累计装潢销售台次",
                    "累计装潢销售总额","装潢销售进度","装潢销售进度差","装潢用品销售率","装潢用品销售率差异",
                    "平均单车装潢销售额","单车装潢金额差异",

                    "养护产品销售率目标","养护产品销售额目标","目标养护产品单车销售额","累计养护产品销售台次","累计养护产品销售总额",
                    "养护产品销售进度","养护产品销售进度差","养护产品销售率","养护产品销售率差异","平均单车养护产品额","单车养护产品额差异"
                };
                sheet.AutoSizeColumns = new List<string>
                {

                };
                foreach (var carbrand in carbrands)
                {
                    var brandreport = AfterSaleCarKPIService.GetCarDecoReport(Month.Year, Month.Month, Store.Id, carbrand.Id);
                    brandreport.CarBrand = carbrand.Name;
                    sheet.AddRow(brandreport);
                }
                //var brandreportTotal = AfterSaleCarKPIService.GetCarTypeReport(Month.Year, Month.Month, Store.Id);
                //brandreportTotal.CarBrand = "合计";
                //sheet.AddRow(brandreportTotal);

                sheet.Fill();
                return File(exceldoc.GetBinaryData(), "application/vnd ms-excel", "车型装潢KPI(" + Month.Year + "-" + Month.Month + ").xlsx");

            }
        }

        public ActionResult SAKpi()
        {
            using (var t = new Transtion())
            {
                t.Inject(ReportService);
                t.Inject(AfterSaleCarKPIService);
                t.Inject(EmployeeService);

                int[] employids = EmployeeService.GetEnableEmployee(Store.Id, EmployeeType.ServiceConsultant).Select(o => o.Id).ToArray();
                var query = AfterSaleCarKPIService.GetAfterSaleServiceAdvisorMonthTargets(Month.Year, Month.Month, employids).ToList();
                var emploies =  from m in query
                                group m by m.Employee into g
                                select g.Key;
                ExcelDocument exceldoc = new ExcelDocument(Server.MapPath("/Templates/Export/服务顾问.xlsx"));
                ExcelSheet sheet = exceldoc.GetSheet(0);
                sheet.Columns = new List<string>
                {
                    "职员姓名","接待前台职业资质","上月末分管客户总数","月初六月未回厂数","本月新增六个月未回厂数","六个月未回厂客户总数","月初四个月未回厂数","本月新增四个月未回厂数","四个月未回厂客户总数","本月A卡转C卡客户数","分管保有客户总数","本月应实施5K保养数","本月应实施10K保养数","本月招徕回厂目标数","本月目标预约入厂台次","本月事故车入厂台次目标","本月维修入厂台次目标","目标入厂台次","本月事故车产值目标","本月维修产值目标","目标售后产值","精品目标销售额","养护产品目标销售额","首次续保台次目标","二次续保台次目标","目标续保台次总数","客户满意度考核目标","累计完成事故入厂台次","累计完成维修入厂台次","累计完成入厂台次","累计完成事故车产值","累计完成维修产值","累计完成售后产值","本月实际实施5K保养数","本月实际实施10K保养数","成功实施招徕客户数","电话成功预约回厂数","累计实现预约回厂数","准时交车客户数","当日交车客户数","累计精品销售额","累计养护产品销售额","首次续保实际完成台次","二次续保实际完成台次","续保台次完成总数","流失客户重新回厂数","满意度回访平均得分","事故车入厂台次完成率","维修车入厂台次完成率","目标入厂台次完成率","事故车产值完成率","维修车产值完成率","目标产值完成率","平均单车维修产值","5K保养实施率","10K保养实施率","招徕实施率","招徕实施完成差异","预约成功率","失约率","准时交车率","精品销售额完成率","精品销售率进度差","单车精品销售额","养护产品销售完成率","养护产品完成进度差","首次续保完成率","二次续保完成率","续保目标完成率","事实客户流失率","隐性客户流失率","流失客户回归率"


                };
                sheet.AutoSizeColumns = new List<string>
                {

                };
                foreach (var employ in emploies)
                {
                    var employreport = AfterSaleCarKPIService.GetSAReport(Month.Year, Month.Month, new int[] { employ.Id });
                    employreport.EmployName = employ.Name;
                    employreport.WorkYear = employ.WorkYear;
                    sheet.AddRow(employreport);
                }

                sheet.Fill();
                return File(exceldoc.GetBinaryData(), "application/vnd ms-excel", "服务顾问KPI(" + Month.Year + "-" + Month.Month + ").xlsx");

            }
        }

        public ActionResult WMKpi()
        {
            using (var t = new Transtion())
            {
                t.Inject(ReportService);
                t.Inject(AfterSaleCarKPIService);
                t.Inject(EmployeeService);

                int[] employids = EmployeeService.GetEnableEmployee(Store.Id, EmployeeType.WorkshopSupervisor).Select(o => o.Id).ToArray();
                var query = AfterSaleCarKPIService.GetAfterSaleWorkshopManagerMonthConfigs(Month.Year, Month.Month, employids).ToList();
                var emploies = from m in query
                               group m by m.Employee into g
                               select g.Key;
                ExcelDocument exceldoc = new ExcelDocument(Server.MapPath("/Templates/Export/车间主管.xlsx"));
                ExcelSheet sheet = exceldoc.GetSheet(0);
                sheet.Columns = new List<string>
                {
                    "职员姓名","接待前台职业资质","部门机师总数","部门设备工具总数","本月工单总数","本月累计入厂台次","本月已交车总数",
                    "技工出勤时间总和","部门定额工时累计总数","部门工位总数","功能完好设备/工具数","无效工单数量","预计时间前交车总数",
                    "当日入厂当日交车总数","技工实际作业时间总和","部门实际工时累计总数","交车后回厂返修总数","设备完好率",
                    "人均工位数","工位周转率","工单报废率","一次修复率","工时效率","生产利用率","当天交车率","准时交车率"
                };
                sheet.AutoSizeColumns = new List<string>
                {

                };
                foreach (var employ in emploies)
                {
                    var employreport = AfterSaleCarKPIService.GetWMReport(Month.Year, Month.Month, new int[] { employ.Id }, Store.Id);
                    employreport.EmployName = employ.Name;
                    employreport.WorkYear = employ.WorkYear;
                    sheet.AddRow(employreport);
                }

                sheet.Fill();
                return File(exceldoc.GetBinaryData(), "application/vnd ms-excel", "车间主管KPI(" + Month.Year + "-" + Month.Month + ").xlsx");

            }
        }

        public ActionResult CSKpi()
        {
            using (var t = new Transtion())
            {
                t.Inject(ReportService);
                t.Inject(AfterSaleCarKPIService);
                t.Inject(EmployeeService);

                int[] employids = EmployeeService.GetEnableEmployee(Store.Id, EmployeeType.AccountSpecialist).Select(o => o.Id).ToArray();
                int[] saeployids = EmployeeService.GetEnableEmployee(Store.Id, EmployeeType.ServiceConsultant).Select(o => o.Id).ToArray();
                var query = AfterSaleCarKPIService.GetAfterSaleCustomerServiceDayInputs(Month.Year, Month.Month, employids).ToList();
                var emploies = from m in query
                               group m by m.Employee into g
                               select g.Key;
                ExcelDocument exceldoc = new ExcelDocument(Server.MapPath("/Templates/Export/客户专员.xlsx"));
                ExcelSheet sheet = exceldoc.GetSheet(0);
                sheet.Columns = new List<string>
                {
                    "职员姓名","客服专员职业资质","人数","本月累计入厂台次","本月新增六月未回厂客户数","本月新增四月未回厂客户数","累计维修后回访数",
                    "六月未回厂客户回访数","四月未回厂客户回访数","流失客户重新回厂数1","维修客户回访率","六月未回厂客户回访率","四月未回厂客户回访率",
                    "流失客户回归率"
                };
                sheet.AutoSizeColumns = new List<string>
                {

                };
                foreach (var employ in emploies)
                {
                    var employreport = AfterSaleCarKPIService.GetCSReport(Month.Year, Month.Month, new int[] { employ.Id },saeployids, Store.Id);
                    employreport.EmployName = employ.Name;
                    employreport.WorkYear = employ.WorkYear;
                    employreport.ZRS = emploies.Count();
                    sheet.AddRow(employreport);
                }

                sheet.Fill();
                return File(exceldoc.GetBinaryData(), "application/vnd ms-excel", "客服专员KPI(" + Month.Year + "-" + Month.Month + ").xlsx");

            }
        }

        public ActionResult SPKpi()
        {
            using (var t = new Transtion())
            {
                t.Inject(ReportService);
                t.Inject(SparePartService);

                ExcelDocument exceldoc = new ExcelDocument(Server.MapPath("/Templates/Export/零部件.xlsx"));
                ExcelSheet sheet = exceldoc.GetSheet(0);
                sheet.Columns = new List<string>
                {
                    "月份","库存成本总量目标","月初库存成本","月末库存成本","本月库存均值","日均备件销售成本","月备件销售成本",
                    "月计划订货成本","月全部订货成本","月备件收入","月均保修备件收入","呆滞库存备件成本","死库存备件成本",
                    "申请保修总件数","保修合格件数","缺件未维修台次","月维修总台次","回收零件总数","发出零件总数","平均在库天数"
                    ,"备件周转率","备件保修合格率","呆库存率","死库存率","呆库存变化率","死库存变化率","正常订货率"
                    ,"备件满足率","零件回收及时率"
                };
                sheet.AutoSizeColumns = new List<string>
                {

                };
                var employreport = SparePartService.GetSPReport(Month.Year, Month.Month, Store.Id);
                employreport.YF = Month.Month;
                sheet.AddRow(employreport);

                sheet.Fill();
                return File(exceldoc.GetBinaryData(), "application/vnd ms-excel", "零部件(" + Month.Year + "-" + Month.Month + ").xlsx");

            }
        }
        #endregion

    }
}
